﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace ScrumProject.Models
{
    public class SprintRepository : IDisposable
    {
        private SMRPO6Context db = new SMRPO6Context();

        public void AddSprint(Sprint sprint)
        {
            using (var db = new SMRPO6Context())
            {
                db.Database.ExecuteSqlCommand("exec dbo.InsertSprint @DateTo, @DateFrom, @Velocity,@ProjectId",
                                             new SqlParameter("DateTo", sprint.DateTo),
                                             new SqlParameter("DateFrom", sprint.DateFrom),
                                             new SqlParameter("Velocity", sprint.Velocity),
                                             new SqlParameter("ProjectId", sprint.ProjectId));
                db.SaveChanges();

            }
        }
        public void UpdateSprint(Sprint sprint)
        {
            using (var db = new SMRPO6Context())
            {
                db.Database.ExecuteSqlCommand("exec dbo.UpdateSprint @Id, @DateFrom, @DateTo, @Velocity",
                                              new SqlParameter("Id", sprint.Id),
                                             new SqlParameter("DateFrom", sprint.DateFrom),
                                             new SqlParameter("DateTo", sprint.DateTo),
                                             new SqlParameter("Velocity", sprint.Velocity));
                db.SaveChanges();

            }
        }

        public Sprint GetSprintByStartDate(DateTime dateTime)
        {
            return (from Sprint in db.Sprints
                    where Sprint.DateFrom == dateTime
                    select Sprint).SingleOrDefault();
        }

        public Sprint GetSprintByEndDate(DateTime dateTime)
        {
            return (from Sprint in db.Sprints
                    where Sprint.DateTo == dateTime
                    select Sprint).SingleOrDefault();
        }

        public IQueryable<Sprint> GetAllSprints()
        {
            return db.Sprints;
        }

        public IQueryable<Sprint> GetAllSprints(int id)
        {
            return from Sprint in db.Sprints
                   where Sprint.Id == id
                   select Sprint;
        }

        public IQueryable<Sprint> GetAllSprintsByProject(int id)
        {
            return from Sprint in db.Sprints
                   where Sprint.ProjectId == id
                   select Sprint;
        }

        public Sprint GetSprint(int id)
        {
            return (from Sprint in db.Sprints
                    where Sprint.Id == id
                    select Sprint).SingleOrDefault();
        }

        public IQueryable<RealizedWork> GetRealizedWorkForEntireSprint(int sprintId)
        {

            return from Sprint in db.Sprints
                   join Story in db.Stories on sprintId equals Story.SprintId
                   join Task in db.Tasks on Story.Id equals Task.StoryId
                   join RealizedWork in db.RealizedWorks on Task.Id equals RealizedWork.TaskId
                   //where Story.Status != "inactive  "
                   select RealizedWork;

        }

        public IQueryable<RealizedWork> GetRealizedWorkForEntireSprintBtDate(Sprint sprint,DateTime dateSprint)
        {

            return from Sprint in db.Sprints
                   join Story in db.Stories on Sprint.Id equals Story.SprintId
                   join Task in db.Tasks on Story.Id equals Task.StoryId
                   join RealizedWork in db.RealizedWorks on Task.Id equals RealizedWork.TaskId
                   where RealizedWork.DateOfWorking == dateSprint
                   select RealizedWork;

        }
        public double? GetRealizedWorkForEntireSprintSum(Sprint sprint, DateTime dateSprint)
        {

            return (from Sprint in db.Sprints
                    join Story in db.Stories on Sprint.Id equals Story.SprintId
                    join Task in db.Tasks on Story.Id equals Task.StoryId
                    join RealizedWork in db.RealizedWorks on Task.Id equals RealizedWork.TaskId
                    where RealizedWork.DateOfWorking == dateSprint
                    select RealizedWork).Sum(RealizedWork => RealizedWork.WorkDone);
            
            
            


        }

        public void DeleteSprint(Sprint sprint)
        {
            using (var db = new SMRPO6Context())
            {
                db.Sprints.Remove(sprint);
                db.SaveChanges();
            }

        }
        public IQueryable<DateTime> GetAllSprintStartDatesByProject(int id) {

            return from Sprint in db.Sprints
                   where Sprint.ProjectId == id
                   select Sprint.DateFrom;
        }
        public IQueryable<DateTime> GetAllSprintStartDatesByProject1(int id,int sprintId)
        {
            if (sprintId == null)
            {

                return from Sprint in db.Sprints
                       where Sprint.ProjectId == id
                       select Sprint.DateFrom;
            }
            else
            {
                return from Sprint in db.Sprints
                       where Sprint.ProjectId == id && Sprint.Id != sprintId
                       select Sprint.DateFrom;
            }
        }
        public IQueryable<DateTime> GetAllSprintEndDatesByProject(int id) {

            return from Sprint in db.Sprints
                   where Sprint.ProjectId == id
                   select Sprint.DateTo;
        }
        public IQueryable<DateTime> GetAllSprintEndDatesByProject1(int id, int sprintId)
        {
            if (sprintId == null)
            {
                return from Sprint in db.Sprints
                       where Sprint.ProjectId == id
                       select Sprint.DateTo;
            }
            else
            {
                return from Sprint in db.Sprints
                       where Sprint.ProjectId == id && Sprint.Id != sprintId
                       select Sprint.DateTo;
            }
        }
        public void Save()
        {
            db.SaveChanges();
        }

        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    db.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}